package main.util;

import java.io.FileWriter;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class SqlConstants {
	
	public static Integer DISPLAYNO=22;
	
	public static Map<String, Integer> getStaffAboutCol(Row row,int startCol,int endCol){
		Map<String,String> staffComment=new HashMap<String, String>();
		staffComment.put("staffId", "matchNames,工号,登录工号,登陆工号,账号ID（GX0+四位账号）,账号,新一代客服系统工号,");
		staffComment.put("staffName", "matchNames,姓名,姓名（同广西新CRM账号名称）,员工姓名,");
		staffComment.put("mobilePhone", "matchNames,电话,手机号码,手机号,");
		staffComment.put("serviceTypeId", "matchNames,业务类型,平台代码,");
		staffComment.put("orgaId", "matchNames,组织ID,班组ID,组织机构ID,");//
		staffComment.put("orgaName", "matchNames,组织机构名称,");//组织机构名称
		staffComment.put("roleId", "matchNames,角色名称,角色,");//角色名称
		staffComment.put("email", "matchNames,邮箱,");//角色名称
		staffComment.put("CRM", "matchNames,CRM,关联省端BOSS账号,");
		staffComment.put("BOSS", "matchNames,BOSS,关联CRM系统工号,");
		
		//组织机构
		staffComment.put("oldOrgaIdLine", "matchNames,老组织ID,老班组ID,oldOrgaId,");
		staffComment.put("newOrgaIdLine", "matchNames,新组织ID,新班组ID,");
		
		//新老工号映射
		
		
		Map<String,Integer> staffAbout=new HashMap<String, Integer>();
		for(int i=startCol;i<endCol+1;i++){
			String colName=getColValue(row, i).trim();
			String colKey=ArrayUtil.containStringValue(staffComment, colName);
			if(colKey!=null){
				staffAbout.put(colKey, i);
			}

		}
		return staffAbout;
	}
	
	//获取生成的sql
	public static String getSql(String tableName,LinkedHashMap<String, String> map){
		//String items="";
		String values="";
		StringBuffer sb=new StringBuffer("INSERT INTO `"+tableName+"` (");
		
		for (Entry<String, String> entry : map.entrySet()){
			String value=map.get(entry.getKey());
			if(value!=null){
				sb.append(entry.getKey()+",");
				values+="'"+value+"',";
			}
		}
		
		sb.deleteCharAt(sb.length()-1);
		sb.append(") values (");
		sb.append(values);
		sb.deleteCharAt(sb.length()-1);
		sb.append(");\n");
		
		//System.out.println(sb.toString());
		
		return sb.toString();
	}
	
	//根据模板作为列，生成的sql
		public static String getSql(String tableName,LinkedHashMap<String, String> map,String[] templates){
			//String items="";
			String values="";
			StringBuffer sb=new StringBuffer("INSERT INTO `"+tableName+"` (");
			
			for (String template : templates){
				String value=map.get(template);
				if(value!=null){
					sb.append(template+",");
					values+="'"+value+"',";
				}
			}
			
			sb.deleteCharAt(sb.length()-1);
			sb.append(") values (");
			sb.append(values);
			sb.deleteCharAt(sb.length()-1);
			sb.append(");\n");
			
			return sb.toString();
		}
		
		public static void setColValue(Row row,int col,String value){
			if(value==null){
				return;
			}
			Cell cell=row.getCell(col);
			if(cell==null){
				row.createCell(col);
			}
			row.getCell(col).setCellValue(value);
		}
		
		/*去掉字符串为空格的值*/
		public static String getColValue(Row row,int col){
			if(row==null){
				return "";
			}
			Cell cell=row.getCell(col);
			
			if(cell==null){
				return "";
			}
			
			if(CellType.NUMERIC.equals(cell.getCellTypeEnum())){
				Double d=cell.getNumericCellValue();
				return new DecimalFormat("#").format(d);
			}
			return cell.toString().replaceAll("\\s", "");
		}
		
		public static void topWriteHead(FileWriter fw){
			try {
				fw.write("/*\n");
				//fw.write("Host           : 192.168.111.45:23307\n");
				fw.write("Host           : 192.168.123.244:20001\n");//郑州环境
				fw.write("Database       : ngmtt\n\n\n");
				fw.write("Author:  王兴飞\n");
				fw.write("Tel:  15637909515\n");
				fw.write("Date: "+new Date()+"\n");
				fw.write("*/\n");
				
				fw.write("use ngmtt;\n");
				fw.write("set names utf8;\n");
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
		//生成sql语句的
		public static void topWriteEnd(FileWriter fw) {
			// TODO Auto-generated method stub
			try {
				fw.write("commit;\n");
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
}
